package cn.com.simple.learning.core.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EasyExcelUtil {

    private static final Logger logger = LoggerFactory.getLogger(EasyExcelUtil.class);

    public static void main(String[] args) {
        compositeFill();
    }

    public static void compositeFill() {

        long startTime = System.currentTimeMillis();


        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        // {} 代表普通变量 {.} 代表是list的变量 {前缀.} 前缀可以区分不同的list
        String templateFileName = "C:\\Users\\jianxing.jia\\Desktop\\composite.xlsx";

        String fileName = "compositeFill" + "_" + System.currentTimeMillis() + ".xlsx";

        // 方案1
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(fileName).withTemplate(templateFileName).build();
            List<FillData> data1List = data();
            List<FillData> data2List = data();

            WriteSheet writeSheet = EasyExcel.writerSheet().build();

            FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
            excelWriter.fill(new FillWrapper("data1", data1List), fillConfig, writeSheet);

            // 如果有多个list 模板上必须有{前缀.} 这里的前缀就是 data1，然后多个list必须用 FillWrapper包裹
            FillWrapper fillWrapper = new FillWrapper("data2", data2List);
            excelWriter.fill(fillWrapper, writeSheet);

            // excelWriter.fill(new FillWrapper("data3", data()), writeSheet);

            Sheet cachedSheet = excelWriter.writeContext().writeSheetHolder().getCachedSheet();
            Sheet sheet = excelWriter.writeContext().writeSheetHolder().getSheet();

            int startRow = 5;
            for (int j = 0; j < data2List.size(); j++) {
                startRow++;
                int startColumn = 1;
                Row row;
                String materialCode;
                if (startRow == 6) {
                    row = cachedSheet.getRow(startRow);
                    // 获取指定单元格中的 物料编码
                    materialCode = row.getCell(0).getStringCellValue();
                    logger.info("第七行，materialCode：{}", materialCode);
                } else {
                    row = sheet.getRow(startRow);
                    materialCode = row.getCell(0).getStringCellValue();
                    // logger.info("materialCode：{}", materialCode);
                }
                for (int i = 0; i < data1List.size(); i++) {
                    startColumn++;
                    // 获取指定单元格中的 柜台编码
                    String marsCode = String.valueOf(cachedSheet.getRow(1).getCell(startColumn).getNumericCellValue());
                    // logger.info("marsCode：{}", marsCode);
                    // 查询数据库操作
                    // 根据从单元格中获取到的 物料编码和柜台编码，到柜台物料申请明细表中获取对应的数量
                    row.createCell(startColumn).setCellValue(materialCode + marsCode);
                }
            }
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
        long endTime = System.currentTimeMillis();
        long costTime = endTime - startTime;
        logger.info("costTime, {}", costTime);
    }

    public static List<FillData> data() {
        List<FillData> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            FillData fillData = new FillData();
            list.add(fillData);
            fillData.setName("张三");
            fillData.setNumber(5.2);
            fillData.setDate(new Date());
            fillData.setMaterial("物料数量");
        }
        return list;
    }

    public static class FillData {
        private String name;
        private double number;
        private Date date;
        private String material;

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public double getNumber() {
            return number;
        }

        public void setNumber(double number) {
            this.number = number;
        }

        public Date getDate() {
            return date;
        }

        public void setDate(Date date) {
            this.date = date;
        }

        public String getMaterial() {
            return material;
        }

        public void setMaterial(String material) {
            this.material = material;
        }
    }
}
